import time
import json
from datetime import datetime
from sp_api.api import Catalog
from sp_api.base import Marketplaces, Credentials
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from app.intereface_amazon.amazon1 import get_client
from app.intereface_amazon.databasemodel import AmazonProduct, Base
from app.intereface_amazon.amazon_config import SP_API_CREDENTIALS, MARKETPLACE_CONFIG, DB_CONFIG
 
class AmazonProductDownloader:
    def __init__(self, marketplace_id='ATVPDKIKX0DER', db_url=None):
        """
        初始化亚马逊产品下载器
        
        参数:
            marketplace_id: 市场ID，默认为美国市场
            db_url: 数据库连接URL
        """
        self.marketplace_id = marketplace_id
        self.marketplace = Marketplaces.US  # 默认使用美国市场
        self.client=get_client()
        # 设置认证信息 
        # 初始化Catalog API
        # self.credentials = Credentials(
        #     lwa_client_id=self.client.lwa_client_id,
        #     lwa_client_secret=self.client.lwa_client_secret,
        #     refresh_token=self.client.refresh_token,
        #     lwa_app_id=self.client.lwa_app_id,
        #     token_type=self.client.token_type,
        #     expires_at=self.client.expires_at
        # )

        self.credentials = Credentials(
            lwa_client_id=self.client.lwa_client_id,
            lwa_client_secret=self.client.lwa_client_secret,
            refresh_token=self.client.refresh_token,
            lwa_app_id=self.client.lwa_app_id,
            aws_access_key_id=self.client.aws_access_key_id,
            aws_secret_access_key=self.client.aws_secret_access_key,
            token_type=self.client.token_type,
            expires_at=self.client.expires_at
        )
        self.catalog_api = Catalog(
            marketplace=self.marketplace,
            credentials=self.credentials
        )
        
        # 初始化数据库连接
        if db_url:
            self.engine = create_engine(db_url)
        else:
            self.engine = create_engine(DB_CONFIG['url'])
        Base.metadata.create_all(self.engine)
        self.Session = sessionmaker(bind=self.engine)
    
    def download_products(self, asin_list, batch_size=20):
        """
        批量下载亚马逊产品信息
        
        参数:
            asin_list: ASIN列表
            batch_size: 每批处理的ASIN数量，默认为20
        
        返回:
            下载结果列表
        """
        download_results = []
        session = self.Session()
        
        try:
            for i in range(0, len(asin_list), batch_size):
                batch = asin_list[i:i+batch_size]
                
                try:
                    # 调用Catalog API获取产品信息
                    response = self.catalog_api.get_catalog_items(
                        asins=batch,
                        includedData=['attributes', 'identifiers', 'images', 'productTypes', 'salesRanks', 'summaries']
                    )
                    
                    # 处理响应
                    if response.payload and 'items' in response.payload:
                        for item in response.payload['items']:
                            try:
                                # 提取产品信息
                                product_data = self._extract_product_data(item)
                                
                                # 检查产品是否已存在
                                existing_product = session.query(AmazonProduct).filter_by(asin=product_data['asin']).first()
                                
                                if existing_product:
                                    # 更新现有产品
                                    for key, value in product_data.items():
                                        setattr(existing_product, key, value)
                                    existing_product.updated_at = datetime.now()
                                else:
                                    # 创建新产品
                                    new_product = AmazonProduct(**product_data)
                                    session.add(new_product)
                                
                                download_results.append({
                                    'asin': product_data['asin'],
                                    'status': 'success'
                                })
                                
                            except Exception as e:
                                download_results.append({
                                    'asin': item.get('asin', 'unknown'),
                                    'status': 'error',
                                    'message': str(e)
                                })
                                print(f"处理产品时出错: {e}")
                                continue
                    
                    # 提交事务
                    session.commit()
                    
                    # 添加延迟以避免API限制
                    time.sleep(1)
                    
                except Exception as e:
                    session.rollback()
                    download_results.append({
                        'batch': i // batch_size + 1,
                        'status': 'error',
                        'message': str(e)
                    })
                    print(f"处理ASIN批次时出错: {e}")
                    continue
                
        finally:
            session.close()
        
        return download_results
    
    def _extract_product_data(self, item):
        """
        从API响应中提取产品数据
        
        参数:
            item: API响应中的产品项
        
        返回:
            产品数据字典
        """
        asin = item.get('asin')
        attributes = item.get('attributes', {})
        identifiers = item.get('identifiers', {})
        images = item.get('images', [])
        product_types = item.get('productTypes', [])
        sales_ranks = item.get('salesRanks', [])
        summaries = item.get('summaries', [])
        
        # 提取主图URL
        main_image_url = None
        if images and len(images) > 0:
            main_image_url = images[0].get('link')
        
        # 提取所有图片URL
        image_urls = []
        if images:
            image_urls = [img.get('link') for img in images if img.get('link')]
        
        # 提取销售排名
        sales_rank = None
        if sales_ranks and len(sales_ranks) > 0:
            sales_rank = sales_ranks[0].get('rank')
        
        # 提取产品类型
        product_type = None
        if product_types and len(product_types) > 0:
            product_type = product_types[0].get('productType')
        
        # 构建产品数据字典
        product_data = {
            'asin': asin,
            'sku': identifiers.get('sku'),
            'title': attributes.get('title'),
            'brand': attributes.get('brand'),
            'bullet_points': json.dumps(attributes.get('bullet_point', [])) if attributes.get('bullet_point') else None,
            'description': attributes.get('description'),
            'item_type': attributes.get('item_type'),
            'manufacturer': attributes.get('manufacturer'),
            'part_number': attributes.get('part_number'),
            'product_tax_code': attributes.get('product_tax_code'),
            'standard_price': attributes.get('standard_price', {}).get('amount') if attributes.get('standard_price') else None,
            'currency': attributes.get('standard_price', {}).get('currency') if attributes.get('standard_price') else None,
            'main_image_url': main_image_url,
            'image_urls': json.dumps(image_urls) if image_urls else None,
            'product_type': product_type,
            'sales_rank': sales_rank,
            'category': sales_ranks[0].get('displayGroup') if sales_ranks and len(sales_ranks) > 0 else None,
            'product_link': f"https://www.amazon.com/dp/{asin}",
            'status': 'active'
        }
        
        return product_data
    
    def save_results_to_file(self, results, output_file='download_results.json'):
        """
        将下载结果保存到JSON文件
        
        参数:
            results: 下载结果列表
            output_file: 输出文件名
        """
        with open(output_file, 'w', encoding='utf-8') as f:
            json.dump(results, f, indent=4)
        
        print(f"下载结果已保存到 {output_file}")

    def save_products_to_db(self, products_data):
        """
        将产品数据保存到数据库，如果存在则更新
        
        参数:
            products_data: 产品数据列表，每个元素是一个字典
        """
        session = self.Session()
        try:
            for product_data in products_data:
                try:
                    # 检查产品是否已存在（使用asin作为唯一标识）
                    existing_product = session.query(AmazonProduct).filter_by(asin=product_data['asin']).first()
                    
                    if existing_product:
                        # 更新现有产品
                        for key, value in product_data.items():
                            setattr(existing_product, key, value)
                        existing_product.updated_at = datetime.now()
                        print(f"更新产品: {product_data['asin']}")
                    else:
                        # 创建新产品
                        new_product = AmazonProduct(**product_data)
                        session.add(new_product)
                        print(f"创建新产品: {product_data['asin']}")
                    
                except Exception as e:
                    print(f"处理产品 {product_data.get('asin', 'unknown')} 时出错: {e}")
                    continue
            
            # 提交事务
            session.commit()
            print("所有产品数据已保存到数据库")
            
        except Exception as e:
            session.rollback()
            print(f"保存产品数据到数据库时出错: {e}")
            raise
        
        finally:
            session.close()

# 使用示例
if __name__ == "__main__":
    # 数据库连接URL
    db_url = "mysql+pymysql://root:Zb_200407@gz-cdb-d4j7h16x.sql.tencentcdb.com:22333/walmart5"
    
    # 创建下载器实例
    downloader = AmazonProductDownloader(db_url=db_url)
    
    # 示例产品数据
    products_data = [
        {
            'asin': 'B07ZPKN6YR',
            'sku': 'TEST-SKU-001',
            'title': 'Test Product 1',
            'brand': 'Test Brand',
            'bullet_points': json.dumps(['Feature 1', 'Feature 2']),
            'description': 'This is a test product description.',
            'item_type': 'test-item-type',
            'manufacturer': 'Test Manufacturer',
            'part_number': 'TEST-PART-001',
            'product_tax_code': 'A_GEN_TAX',
            'standard_price': 19.99,
            'currency': 'USD',
            'main_image_url': 'https://example.com/image1.jpg',
            'image_urls': json.dumps(['https://example.com/image1.jpg', 'https://example.com/image2.jpg']),
            'product_type': 'PRODUCT',
            'sales_rank': 1000,
            'category': 'Test Category',
            'product_link': 'https://www.amazon.com/dp/B07ZPKN6YR',
            'status': 'active'
        }
    ]
    
    # 保存产品数据到数据库
    downloader.save_products_to_db(products_data)